OpenEdge Reporting:<br />Query/Results for UNIX Adding a WHERE clause

This section explains how to create a WHERE clause to limit the records that appear in the report. In this example, the WHERE clause limits records in the report to customers who have Spike Smith (SLS) or Bubba Brawn (BBB) as their sales representative, and have a current balance of more than $1,000. Thus, the report displays the records according to the following Where clause definition:

`WHERE` (`Sales Rep =` `SLS` `OR Sales Rep =` `BBB) AND Balance >` `1000`.

In this type of WHERE clause, you compare field values to a constant value to get the same set of records every time you run the report. In another type of WHERE clause, using Ask mode, you can enter a different value to compare each time. Thus, each time you run the report, Results prompts you for a value.

Get the Order Line Report.

Select Where in the Reports module.

Because there is more than one file in the list of files, a highlight bar appears that lets you select the file you want to use to build the WHERE clause:

Select the Customer file. Several windows appear that let you build the WHERE clause.

The first window lists the fields in the Customer file:

If you are an experienced user, press GET to enter the Expert mode and type in the WHERE clause expression. In this exercise, you can let the WHERE clause builder guide you. For details on using the Expert mode to build a WHERE clause, see the appropriate section of Chapter 4, " Query Module."

Select the Sales-rep field. The Comparisons window contains options for building onto your WHERE clause. The beginning of the WHERE clause appears in the Expression window:

Notice that Sales-rep is prefixed by mysports.customer. This means that the Sales-rep record is in the customer file in the mysports database.

Also, the Comparisons window lists all the choices you can make to character fields. However, sometimes this list contains fewer choices depending on the field you choose. (For example, if you chose Credit-Limit, the Comparison window would only list comparisons you can make to numbers since Max-credit is a numeric field value).

Select the Equal (=) comparison. If you select the wrong operator by mistake, press END to return to the Comparisons window and select a different comparison. The screen adds an equal ( = ) symbol to the WHERE clause, and prompts you to enter a number to compare to Sales-rep:

If you press PUT, you can enter a prompt that appears when the report runs. For example, you can have the screen prompt for a value to compare the sales representative field. (Anyone running the report could decide what sales representative they want the WHERE clause to select.) For more information on this prompt, see the section "Adding a WHERE clause with Ask mode" section. Enter a value to compare to Sales-rep.

Type sls and press RETURN.

Results prompts you to enter more values for sales representative:

If you select Yes, you can add more criteria to compare with sales representative. For example, you can build a WHERE clause that selects records for customers that have SLS OR BBB as the sales rep. (Then the record must meet one of these requirements for Results to include it in your report)

Because you are using the Equal comparison, Results combines the criteria automatically with an OR operator. (If you were using the Not Equal comparison, Results would automatically combine the criteria using AND.) The Equal and Not Equal comparisons represent special cases in the WHERE clause builder. If you use them and enter more than one set of values for a field, Results automatically puts the clause within parentheses. (Results evaluates expressions inside parentheses first.)

If you select No, you can leave the WHERE clause as it is or you can build on it using other fields and criteria.

Select Yes.

The WHERE clause now contains an OR:

Type bbb and press RETURN.

Results prompts you to enter more values for sales representative.

Select No.

Results prompts you to choose whether you want to enter more selection criteria.

Select Yes.

Results prompts you to select how to combine the first part of the WHERE clause with the next part:

This window illustrates an important feature of the Where option. You can combine multiple criteria using AND or OR to build larger WHERE clauses. When you use AND, you select the records that are true for both sets of criteria. When you use OR, you select the records that satisfy either criteria.

For example, you can build a WHERE clause that selects records for customers that have BBB or SLS as their sales representative and also have a current balance of more than $1,000.

Select AND.

The WHERE clause now contains an AND:

Because you are using the Equal comparison, Results automatically places parentheses around parts of the WHERE clause so you can tell how to evaluate it. For example, Results first evaluates criteria within parentheses and finds the records that meet that criteria. Then it compares that set of records with the remaining criteria. In this case, it finds the records that have either of these two sales representatives. Then from that set of records Results finds the records that also meet the next (AND) criteria. For more information about evaluating expressions, see Appendix F "Order of Evaluation."

If you want to use parentheses to group expressions when you use other types of criteria (other than Equal or Not Equal), you must define or edit the WHERE clause using Expert Mode. For more information about Expert Mode, see Chapter 4, " Query Module."

Build the following WHERE clause information:


`Balance is Greater Than` `1000`

If you make a mistake, press END to back out of the windows.

When you are done with building the WHERE clause, Results prompts you to enter more selection criteria.

Select No. Results clears the screen and displays the Report Info window. The WHERE clause appears in this window:

The entire WHERE clause is too wide to fit on the screen. (If you want to see the entire WHERE clause, select the Info option.)

Run the report on your terminal. This report appears:

The records in the report are for customers whose sales representative is Spike Smith or Bubba Brawn and have a current balance of more than $1,000.

Return to the Reports module window and save this report over the old definition of the Order Line Report.